﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace AdoNet
{
  class DBHelper
  {
    private static readonly string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
    public static int ExecuteNonQuery(string sql,int cmdType,params SqlParameter[] paras)
    {
      SqlConnection connection = new SqlConnection(connStr);
      SqlCommand command = BuildCommand(connection, sql, cmdType, null, paras);
      int count = command.ExecuteNonQuery();
      command.Parameters.Clear();
      connection.Close();
      return count;
    }

    public static object ExecuteScalar(string sql, int cmdType, params SqlParameter[] paras)
    {
      SqlConnection connection = new SqlConnection(connStr);
      SqlCommand command = BuildCommand(connection, sql, cmdType, null, paras);
      object count = command.ExecuteScalar();
      command.Parameters.Clear();
      connection.Close();
      return count;
    }
    public static SqlDataReader ExecuteReader(string sql, int cmdType, params SqlParameter[] paras)
    {
      SqlConnection connection = new SqlConnection(connStr);
      SqlDataReader reader = null;
      SqlCommand command = BuildCommand(connection, sql, cmdType, null, paras);
      try
      {
        reader = command.ExecuteReader(CommandBehavior.CloseConnection);
        command.Parameters.Clear();
      }
      catch(SqlException e)
      {
        connection.Close();
        throw new Exception("查询异常", e);
      }
     
      return reader;
    }
    public static DataSet GetDataSet(string sql, int cmdType, params SqlParameter[] paras)
    {
      DataSet set = new DataSet();
      SqlConnection connection = new SqlConnection(connStr);
      SqlCommand command = BuildCommand(connection, sql, cmdType, null, paras);
      SqlDataAdapter adapter = new SqlDataAdapter(command);
      adapter.Fill(set);
      connection.Close();
      return set;
    }
    public static DataTable GetDataTable(string sql, int cmdType, params SqlParameter[] paras)
    {
      DataTable table = new DataTable();
      SqlConnection connection = new SqlConnection(connStr);
      SqlCommand command = BuildCommand(connection, sql, cmdType, null, paras);
      SqlDataAdapter adapter = new SqlDataAdapter(command);
      adapter.Fill(table);
      connection.Close();
      return table;
    }
    public static bool ExecuteTrans(List<string> listSQl)
    {
      SqlConnection connection = new SqlConnection(connStr);
      connection.Open();
      SqlTransaction transaction = connection.BeginTransaction();
      SqlCommand command = connection.CreateCommand();
      command.Transaction = transaction;
      try
      {
        for (int i = 0; i < listSQl.Count; i++)
        {
          command.CommandText = listSQl[i];
          command.ExecuteNonQuery();
        }
        transaction.Commit();
        return true;
      }
      catch (SqlException e)
      {
        transaction.Rollback();
        throw new Exception("执行事务出现异常", e);
      }
      finally
      {
        transaction.Dispose();
        command.Dispose();
        connection.Close();
      }
    }
    public static bool ExecuteTrans(List<CommandInfo> listCommand)
    {
      SqlConnection connection = new SqlConnection(connStr);
      connection.Open();
      SqlTransaction transaction = connection.BeginTransaction();
      SqlCommand command = connection.CreateCommand();
      command.Transaction = transaction;
      try
      {
        for(int i = 0; i < listCommand.Count; i++)
        {
          command.CommandText = listCommand[i].commandText;
          if (listCommand[i].commandType == 2) command.CommandType = CommandType.StoredProcedure;
          command.Parameters.Clear();
          if (listCommand[i].parameters != null && listCommand[i].parameters.Length > 0)
            command.Parameters.AddRange(listCommand[i].parameters);
          command.Parameters.AddRange(listCommand[i].parameters);
          command.ExecuteNonQuery();
          command.Parameters.Clear();
        }
        transaction.Commit();
        return true;
      }
      catch(SqlException e)
      {
        transaction.Rollback();
        throw new Exception("执行事务出现异常", e);
      }
      finally
      {
        transaction.Dispose();
        command.Dispose();
        connection.Close();
      }
    }
    private static SqlCommand BuildCommand(SqlConnection connection,string sql,int cmdType,
      SqlTransaction transaction, params SqlParameter[] paras)
    {
      SqlCommand command = new SqlCommand(sql, connection);
      if (cmdType == 2) command.CommandType = CommandType.StoredProcedure;
      if (paras != null && paras.Length > 0) command.Parameters.AddRange(paras);
      if (connection.State == ConnectionState.Closed) connection.Open();
      if (transaction != null) command.Transaction = transaction;
      return command;

    }
  }
}
